Database availability and manageability have become increasingly important in many of today's mission-critical environments. It is inevitable that a certain amount of time is necessary for maintenance of a relational database, but it is important to keep this at a minimum. The ability of a database to reorganize data and restore a more efficient data block layout on a disk is important to improve overall database performance and disk space utilization.
A relational database is a database having a plurality of tables, wherein each table has authoritative data disposed there within, and at least one index, wherein the at least one index points to authoritative data within the table so that users of the relational database can access and change the authoritative data disposed within the table by using the at least one index. The index prevents more than one user from changing a single block of authoritative data simultaneously with another user of the relational database. As each single block of authoritative data is modified, the index grabs the data and then stores the single block into a memory location within a storage device.
In existing databases, creating or rebuilding an index to reduce fragmentation causes a basic table to be locked and no Data Management Language (DML) operation is allowed until an index operation is complete. Such a process is time consuming for databases having large tables.
Typically, a conventional maintenance operation performed on an online database, requires that the online database be taken off-line for approximately 12 to 24 hours while the maintenance operation, preferably a defragmentation operation is performed.
As described in U.S. Pat. No. 5,842,222 ('222 patent), and references cited therein which are herein incorporated by reference, as users update, add or delete records from a database, database table and index data blocks on a storage device such as a disk can become fragmented or non-contiguous resulting in an inefficient physical data block layout. The fragmented disk resulting query performance degradation is caused by added disk head movements and increased I/O response time. Additionally inefficient data block layout wastes disk space.
The '222 patent provides a production information system enhanced for availability wherein the '222 further provides a method for providing high availability in a production information system. The information system consists of a primary and a secondary copy running on a dual machine architecture. A backup database is added to the system which is updated from a daily transaction log. Database maintenance and archive procedures are performed periodically at a scheduled time using the backup database. However, the '222 patent does not provide a continuous method for maintaining the backup database according to actual usage, but instead provides for a daily backup of the primary and secondary databases.
A method to enhance availability and manageability of disk space on a database allows users full access to a database during a data reorganization operation. Using a conventional online method for defragmentation requires partitioning a database into a plurality of tables and then performing maintenance on each table one at a time. Such a defragmentation process severely limits access to not only an individual table, but also impacts overall database performance by allowing only limited activity to occur when a user accesses the online database during a defragmentation operation. Typically, the conventional online defragmentation operation can take approximately ten hours.
For example, Oracle8, a database management program developed by the Oracle Corporation located in Redwood Shores, Calif. has partitioned tables and indexes which allow administrators to perform maintenance on the partitioned tables and indexes one partition at a time offline while maintaining other partitions online. Oracle8 minimizes the impact of maintenance activities on data availability. Oracle8i, also developed by the Oracle Corporation improves upon Oracle8 by adding a new feature that allows administrators to perform required maintenance on a partitioned table and index while keeping the partitioned tables and indexes completely online and thus, available or accessible to end-users. Oracle8i improves data availability, query performance, response time and disk space utilization, all of which are important in a mission-critical environment.
However data reorganization considerations are associated with use of an Oracle system. Concurrent activity on a table during an online operation can be problematic. During an online operation, the Oracle systems Oracle8 and Oracle8i recommend that users minimize activities on the base table. Additionally, database activities should impact less than ten percent of the table while an online indexing/partitioning operation is in progress.
Also, Oracle does not recommend running online operations at peak times or running a batch job that modifies large amounts of data during an online data reorganization. When using Oracle during an online operation, a direct load, and import or export cannot be performed.
Oracle also provides a performance penalty for DML operations occurring during an online operation. Oracle estimates a performance overhead for DML operations during an online operation is comparable to a performance penalty of maintaining one extra index.
Currently, Oracle 9i, also developed by the Oracle Corporation, provides the capability for online table and index re-organization of a single table but not simultaneously on an entire regional database.
Existing systems provide a client in communication with an online database, wherein when the online database imported and exported data to an export dump file, the client was prevented from accessing the online database for extended periods of time, preferably ranging from 12 to 24 hours. For example, the architecture of such an off-line method is shown in FIG. 1, wherein the architecture of an existing off-line system comprises: a client device, an online relational database, and a dump file. The method as shown in FIG. 1 limits access to the relational database and is only single or partition table specific.
The present invention overcomes the problems with existing database management methods by providing a batch method for enhancing availability of a relational database, thereby reducing downtime of the relational database.